child

Table Name: child

The child table stores detailed information about children enrolled in a system, including personal details, parental information, enrollment status, and various tracking attributes.

---

Columns

Column Name	Data Type	Constraints	Description
mappedId	int(11)	Primary Key, Not Null	Unique identifier for the child, mapped to the idmapper table.
birthdate	datetime	Nullable	Date and time of the child’s birth.
createdDate	datetime	Nullable	The date and time when the record was created.
dateEnrolled	datetime	Nullable	Date when the child was enrolled in the program/system.
dateOfCompletion	datetime	Nullable	Date when the child's enrollment was completed.
description	varchar(255)	Nullable	Additional description about the child.
domicile	varchar(30)	Nullable	Child’s domicile (permanent residence).
medicalRecordNumber	varchar(30)	Nullable	Identifier for the child’s medical record.
estimatedBirthdate	tinyint(1)	Nullable	Indicates if the birthdate is estimated (1 for true, 0 for false).
fatherFirstName	varchar(35)	Nullable	Father’s first name.
fatherLastName	varchar(30)	Nullable	Father’s last name.
fatherMiddleName	varchar(30)	Nullable	Father’s middle name.
firstName	varchar(30)	Nullable	Child’s first name.
gender	varchar(15)	Default: 'UNKNOWN'	Gender of the child (e.g., Male, Female, Unknown).
lastEditedDate	datetime	Nullable	Timestamp for the last edit made to the child’s record.
lastName	varchar(30)	Nullable	Child’s last name.
middleName	varchar(30)	Nullable	Child’s middle name.
motherFirstName	varchar(30)	Nullable	Mother’s first name.
motherLastName	varchar(30)	Nullable	Mother’s last name.
motherMiddleName	varchar(30)	Nullable	Mother’s middle name.
nic	varchar(20)	Nullable	National Identification Card (NIC) number of the child (if applicable).
nicOwnerFirstName	varchar(30)	Nullable	First name of the person owning the NIC linked to the child.
nicOwnerLastName	varchar(30)	Nullable	Last name of the person owning the NIC linked to the child.
nicOwnerRelation	smallint(6)	Nullable	Relationship of the NIC owner to the child (e.g., parent, guardian).
status	varchar(20)	Default: 'FOLLOW_UP'	Current status of the child (e.g., Enrolled, Completed, Follow-Up).
terminationDate	datetime	Nullable	Date when the child’s enrollment was terminated.
terminationReason	varchar(255)	Nullable	Reason for termination of enrollment.
terminationReasonOther	varchar(255)	Nullable	Additional details about the termination reason, if applicable.
createdByUserId	int(11)	Nullable	User ID of the person who created the record, references the user table.
lastEditedByUserId	int(11)	Nullable	User ID of the person who last edited the record, references the user table.
enrollmentVaccineId	smallint(6)	Nullable	ID of the vaccine associated with the child’s enrollment, references the vaccine table.
armId	int(11)	Nullable	ID of the associated arm (study or program), references the arm table.
zeroDose	tinyint(1)	Nullable	Indicates if the child received a zero-dose vaccine (1 for true, 0 for false).
isExternalFollowedupChild	tinyint(1)	Nullable	Indicates if the child is followed up externally (1 for true, 0 for false).

---

Indexes

1. Primary Key: mappedId
   • Ensures uniqueness for each child record.
2. Foreign Keys:
   • mappedId → idmapper(mappedId)
   • createdByUserId → user(mappedId)
   • lastEditedByUserId → user(mappedId)
   • enrollmentVaccineId → vaccine(vaccineId)
   • armId → arm(armId)
3. Indexes:
   • child_lastEditedByUserId_user_mappedId_FK
   • child_createdByUserId_user_mappedId_FK
   • child_enrVaccId_vaccine_vaccId_idx
   • child_birthdate
   • arm_armId_child_armId_FK_idx
   • child_createdDate
   • idx_child_dateEnrolled_zeroDose (Used for ZeroDoseReport stored procedure).
   • idx_child_dateEnrolled
   • idx_child_fatherFirstName (Optimized for search queries).
   • idx_child_fatherFirstName_firstName

---

Relationships

1. idmapper table:
   • Maps mappedId for unique identification across systems.
2. user table:
   • Tracks who created or edited child records.
3. vaccine table:
   • Links children to specific vaccines received during enrollment.
4. arm table:
   • Associates children with program/study arms.

---

Usage Notes

• The child table is central to tracking a child’s enrollment, vaccination, and follow-up processes.
• Indexes and constraints are optimized for query performance and referential integrity.
• Nullable fields allow flexibility in recording incomplete data while maintaining essential fields like mappedId and status.

---